﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CinemaBookingProject.DBLayer;
using System.Threading;
using System.Configuration;

public class DBCustomer
{
    static internal SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Balder"].ConnectionString);//new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E8;Persist Security Info=True;User ID=dmaa0912_E8;Password=MaaGodt");
    private static object obj = new object();
    private string select = "SELECT PhoneNO, Name, Email FROM CUSTOMER";
    private string delete = "DELETE FROM Customer";
    private DBBooking dbb;

    /// <summary>
    /// A private method used to add "WHERE" in the statement.
    /// </summary>
    /// <param name="query">Insert the old query string</param>
    /// <param name="parameter">The statement after "WHERE"</param>
    /// <returns>Returns the query string</returns>
    private string WhereAdder(string query, string parameter)
    {
        return query + " WHERE " + parameter;
    }


    /// <summary>
    /// A private method used to add "LIKE" in the statement.
    /// </summary>
    /// <param name="query">Insert the old query string</param>
    /// <param name="parameter">The statement in "LIKE"</param>
    /// <returns>Returns the query string</returns>
    private string LikeAdder(string query, string columnName, string parameter)
    {
        return query + " WHERE " + columnName + " LIKE " + parameter;
    }
    

    /// <summary>
    /// A read method to find a customer by phone
    /// </summary>
    /// <param name="phoneNumber"></param>
    /// <returns>A list of customers if there are more in the database with the same number</returns>
    public List<Customer> Read(string phoneNumber)
    {
        Customer cust;
        SqlDataReader rdr = null;
        List<Customer> customers = new List<Customer>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(LikeAdder(select, "PhoneNO", "@PhoneNO"), Connection);
            cmd.Parameters.Add("@PhoneNO", SqlDbType.VarChar).Value = "%" + phoneNumber + "%";

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                cust = new Customer((string)rdr["PhoneNO"]);
                if (rdr["Name"] != null)
                {
                    cust.Name = (string)rdr["Name"];
                }
                if (rdr["Email"] != null)
                {
                    cust.Email = (string)rdr["Email"];
                }
                //cust.Booking = dbb.Read(cust);
                customers.Add(cust);
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return customers;
    }

    /// <summary>
    /// A read method to find all customers
    /// </summary>
    /// <returns>A list of Customers</returns>
    public List<Customer> ReadAll()
    {
        SqlDataReader rdr = null;
        List<Customer> customers = new List<Customer>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(select, Connection);
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Customer cust = new Customer((string)rdr["PhoneNO"]);
                if (rdr["Name"] != null)
                {
                    cust.Name = (string)rdr["Name"];
                }
                if (rdr["Email"] != null)
                {
                    cust.Email = (string)rdr["Email"];
                }
                customers.Add(cust);
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return customers;
    }

    /// <summary>
    /// Insert method for customer
    /// </summary>
    /// <param name="phoneNumber">Always needed for when to find customer</param>
    /// <param name="name">Not necessary and can be null</param>
    /// <param name="email">Not necessary and can be null</param>
    /// <returns>If it goes well it returns +1, if there is an SQL Exception returns -1</returns>
    public int Insert(Customer cust)
    {
        int result = 0;
        try
        {
            Monitor.Enter(obj);
            string insert = "INSERT INTO Customer VALUES (@PhoneNumber, @Name, @Email)";
            Connection.Open();
            SqlCommand cmd = new SqlCommand(insert, Connection);
            cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar).Value = cust.PhoneNo;
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = cust.Name;
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = cust.Email;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
            Monitor.Exit(obj);
        }

        return result;
    }

    /// <summary>
    /// Delete method for customer
    /// </summary>
    /// <param name="phoneNo">To find the specific customer to be deltet from database.</param>
    /// <returns>If it goes well it returns +1, if there is an SQL Exception returns -1</returns>
    public int Delete(string phoneNo)
    {
        int result = 0;
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(WhereAdder(delete, "PhoneNO = @PhoneNO"), Connection);
            cmd.Parameters.Add("@PhoneNO", SqlDbType.VarChar).Value = phoneNo;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return result;
    }
}

